﻿
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Linq;
using com.nm.ApiServer;
using com.nm.ApiServer.Attributes;
using com.nm.Attributes;
using com.nm.library.Helper;
using DistributeAPI.Model;
using NHibernateVertica;
using george.Data;
using System.Data;
using System.Text;
using com.nm;
using com.nm.Helper;
using Vertica.Data.VerticaClient;
using Nancy.Extensions;
using System.Diagnostics;
using com.nm.library;

namespace DistributeAPI
{
    /// <summary>
    /// 企划订单量API
    /// </summary>
    public class NewPlanOrderQuantityHandler : BaseHandler
    {
        public VerticaDataHelper Helper { get; set; } = new VerticaDataHelper();

        #region Excel导入

        /// <summary>
        /// 创建临时表
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="TempTableName"></param>
        /// Writer：梁家健
        /// Create Date：2017-07-17
        public void CreateTempTable(DataTable dt,string TempTableName)
        {
            try
            {
                var sql = $@"CREATE TABLE {TempTableName}
(
    ID int,
    ExtrSource varchar(30),
    CompanyID varchar(30),
    ShopCode varchar(30),
    ShopName varchar(45),
    MaterialID varchar(45),
    TotalQty int,
    SizeLength varchar(15)
)";
                var list =
                    ModelConvertHelper<NewPlanningOrderQuantityModel.ExportModel>.ConvertToModel(dt)
                        .DistinctBy(d => new {d.ShopCode, d.MaterialID}).ToList();
                var i = 1;
                list.ForEach(d =>
                {
                    d.ID = i++;
                    d.CompanyID = UserInfo.UserCompanyId;
                    d.ExtrSource = UserInfo.UserExtrSource;
                });
                var newDT = DataTableHelper.LinqToDataTable(list);
                Helper.ExecuteSqlTran(newDT, TempTableName, sql);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        /// <summary>
        /// 删除临时表
        /// </summary>
        /// <param name="TempTableName"></param>
        /// Writer：梁家健
        /// Create Date：2017-07-17
        public void DropTempTable(string TempTableName)
        {
            try
            {
                Helper.DropTempTable(TempTableName);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        /// <summary>
        /// 插入数据到临时表
        /// </summary>
        /// <param name="list"></param>
        /// Writer：梁家健
        /// Create Date：2017-07-17
        public DataTable ImportPlanningOrderQuantity(string TempTableName)
        {
            var uidTarget = Guid.NewGuid().ToString().Replace("-", "");
            var targetTableName = $@"ngoods.temp{uidTarget}";
            var uidFinalTemp = Guid.NewGuid().ToString().Replace("-", "");
            var finalTempTableName = $@"ngoods.temp{uidFinalTemp}";
            try
            {
                var companyID = UserInfo.UserCompanyId;
                var extrSource = UserInfo.UserExtrSource;
                #region Cache

                DropTempTable(targetTableName);
                //所有店铺的最低深度和麻痹
                CreateSizeRate(TempTableName, targetTableName);

                #endregion Cache
                var list =
                    ReturnTempData(targetTableName);
                var resultList =
                    list.Where(
                        d =>
                            d.ShopCode != null && d.HaveDeep == 1 && d.CompanyID == companyID &&
                            d.ExtrSource == extrSource);
                if (resultList.Any())
                {
                    var tempList =
                        resultList.Where(d => d.IsDeep == 1)
                            .GroupBy(d => new {d.ShopCode, d.MaterialID})
                            .Where(d => d.Sum(r => r.SizeRate) > 0).ToList();
                    var newResultList = new List<NewPlanningOrderQuantityModel.TempModel>();
                    foreach (var itemList in tempList)
                    {
                        var count = itemList.Count();
                        var length = Convert.ToInt32(itemList.First().SizeLength.Substring(0, 1));
                        var newList = count > length ? itemList.Skip(count - length).ToList() : itemList.ToList();
                        var totalQty = newList.First().TotalQty;
                        var sumQty = newList.Sum(p => p.MinQty);
                        //新增和批量导入时,如果skc总量 < 最低深度的合计,则直接把最低深度分给对应的尺码的数量
                        if (totalQty < sumQty)
                        {
                            foreach (var item in newList)
                            {
                                item.SizeQty = item.MinQty;
                            }
                        }
                        else
                        {
                            var sum = newList.Sum(p => p.SizeRate);
                            foreach (var item in newList)
                            {
                                item.SizeRate = item.SizeRate / sum;
                            }

                            //SKC数量－尺码最低深度 ＝剩余skc数量

                            var remainSKC = totalQty - sumQty;

                            foreach (var item in newList)
                            {
                                //计算出单个尺码的skc数量
                                decimal sizeSkc = 0;
                                var itemSizeRate = item.SizeRate;
                                if (itemSizeRate.HasValue)
                                    sizeSkc = (remainSKC ?? 0) * itemSizeRate.Value;
                                var roundSizeSkc = MathHelper.Rounding(Convert.ToDouble(sizeSkc), 0);
                                item.SizeQty = Convert.ToInt32(roundSizeSkc);
                            }

                            #region 判断大小

                            var result = newList.Sum(p => p.SizeQty) + newList.Sum(p => p.MinQty) +
                                         -newList.First().TotalQty;

                            var sortedList = newList.OrderBy(p => p.SizeCodeSort);
                            //判断大小
                            //结果汇总大于skc总量
                            if (result > 0)
                            {
                                foreach (var item in sortedList)
                                {
                                    if (result > 0)
                                    {
                                        if (item.SizeQty > 0)
                                        {
                                            item.SizeQty--;
                                            result--;
                                        }
                                    }
                                }
                            }
                            //结果汇总大于skc总量
                            else if (result < 0)
                            {
                                foreach (var item in sortedList)
                                {
                                    if (result < 0)
                                    {
                                        if (item.SizeQty > 0)
                                        {
                                            item.SizeQty++;
                                            result++;
                                        }
                                    }
                                }
                            }

                            #endregion 判断大小

                            #region 校验性计算

                            //防止出现不加深度之前算出的skc数量都为0的情况
                            //这里再次校验性计算一次,不再判断是否为0
                            result = newList.Sum(p => p.SizeQty) + newList.Sum(p => p.MinQty) +
                                     -newList.First().TotalQty;
                            //判断大小
                            //结果汇总大于skc总量
                            if (result > 0)
                            {
                                foreach (var item in sortedList)
                                {
                                    if (result > 0)
                                    {
                                        item.SizeQty--;
                                        result--;
                                    }
                                }
                            }
                            //结果汇总大于skc总量
                            else if (result < 0)
                            {
                                foreach (var item in sortedList)
                                {
                                    if (result < 0)
                                    {
                                        item.SizeQty++;
                                        result++;
                                    }
                                }
                            }

                            #endregion 校验性计算

                            //最终的skc数量 = 计算出的skc数量 + 尺码对应的最低深度
                            foreach (var item in newList)
                            {
                                item.SizeQty += item.MinQty;
                            }
                        }
                        newResultList.AddRange(newList);
                    }
                    var finalList = newResultList.Select(d => new NewPlanningOrderQuantityModel.CoreModel
                    {
                        CompanyID = d.CompanyID,
                        ExtrSource = d.ExtrSource,
                        ShopID = d.ShopID,
                        ShopCode = d.ShopCode,
                        MaterialID = d.MaterialID,
                        TotalQty = d.TotalQty,
                        SizeID = d.SizeID,
                        SizeMinQty = d.MinQty ?? 0,
                        Qty = d.SizeQty,
                        SizeLength = d.SizeLength,
                        AllowUsed = 0,
                        MofifyDTM = DateTime.Now,
                        Checker = UserInfo.UserID,
                    });
                    DelPlanningOrderQuantity(
                        finalList.DistinctBy(d => new {d.CompanyID, d.ExtrSource, d.ShopID, d.ShopCode, d.MaterialID})
                            .ToList());
                    var finalDT = DataTableHelper.LinqToDataTable(finalList);
                    MergeIntoPlanningOrderQuantity(finalDT, finalTempTableName);
                }
                var errorList = list.Except(resultList);
                var errorDT = DataTableHelper.LinqToDataTable(errorList.DistinctBy(d => new
                {
                    d.CompanyID,
                    d.ExtrSource,
                    d.ShopID,
                    d.ShopCode,
                    d.MaterialID,
                    d.SizeLength
                }));
                return errorDT;
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                DropTempTable(finalTempTableName);
                DropTempTable(targetTableName);
            }
        }

        public void ReComputeSizeRateOfCity(IList<NewPlanningOrderQuantityModel.SizeRateModel> listSizeRate)
        {
            var sum = listSizeRate.Sum(p => p.SizeRate);
            if (sum == 0)
            {
                return;
            }
            foreach (var item in listSizeRate)
            {
                item.SizeRate = item.SizeRate / sum;
            }
        }

        /// <summary>
        /// 删除旧数据
        /// </summary>
        /// <param name="list"></param>
        /// Writer：ljj
        /// Create Date：2017-08-02
        public void DelPlanningOrderQuantity(IList<NewPlanningOrderQuantityModel.CoreModel> list)
        {
            try
            {
                const string sql = @" ( ShopCode = '{0}'
              AND ShopID = '{1}'
              AND MaterialID = '{2}'
            ) {3}";
                var sb = new StringBuilder($@" DELETE
FROM    ngoods.ngoods_DC_Bas_PLOrder 
WHERE   ExtrSource = '{UserInfo.UserExtrSource}'
        AND CompanyID = '{UserInfo.UserCompanyId}'
        AND ");
                var finalSB = new StringBuilder();
                var i = 1;
                foreach (var item in list)
                {
                    sb.AppendLine(string.Format(sql, item.ShopCode, item.ShopID, item.MaterialID,
                        (i == list.Count || i%1000 == 0) ? ";" : " OR "));
                    if (i % 1000 == 0)
                    {
                        finalSB.AppendLine(sb.ToString());
                        sb = new StringBuilder($@" DELETE
FROM    ngoods.ngoods_DC_Bas_PLOrder 
WHERE   ExtrSource = '{UserInfo.UserExtrSource}'
        AND CompanyID = '{UserInfo.UserCompanyId}'
        AND ");
                    }
                    else
                    {
                        if (i == list.Count)
                        {
                            finalSB.AppendLine(sb.ToString());
                        }
                    }
                    i++;
                }
                var cmd = Helper.GetSqlStringCommand(finalSB.ToString());
                Helper.ExecuteNonQuery(cmd);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        /// <summary>
        /// merge
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="TempTableName"></param>
        /// Writer：梁家健
        /// Create Date：2017-07-17
        public void MergeIntoPlanningOrderQuantity(DataTable dt, string TempTableName)
        {
            try
            {
                const string onConditions = @"targetTable.CompanyID=sourceTable.CompanyID
    and targetTable.ExtrSource=sourceTable.ExtrSource
	and targetTable.ShopID=sourceTable.ShopID
	and targetTable.MaterialID=sourceTable.MaterialID
	and targetTable.SizeID=sourceTable.SizeID
	and targetTable.SizeLength=sourceTable.SizeLength";
                var setConditions = $@"TotalQty=sourceTable.TotalQty ,
SizeMinQty=sourceTable.SizeMinQty ,
Qty=sourceTable.Qty ,
Checker=sourceTable.Checker ,
MofifyDTM=sourceTable.MofifyDTM ,
AllowUsed=sourceTable.AllowUsed";
                const string insertConditions =
                    @"ExtrSource,CompanyID,ShopID,ShopCode,MaterialID,SizeID,SizeLength,TotalQty,SizeMinQty,Qty,Checker,MofifyDTM,AllowUsed";
                const string valuesConditions =
                    @"sourceTable.ExtrSource,sourceTable.CompanyID,sourceTable.ShopID,sourceTable.ShopCode,sourceTable.MaterialID,sourceTable.SizeID,sourceTable.SizeLength,sourceTable.TotalQty,sourceTable.SizeMinQty,sourceTable.Qty,sourceTable.Checker,sourceTable.MofifyDTM,sourceTable.AllowUsed";
                Helper.Merge(TempTableName, "ngoods.ngoods_DC_Bas_PLOrder", dt, onConditions, setConditions,
                    insertConditions,
                    valuesConditions, true);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="SourceTableName"></param>
        /// <param name="TargetTableName"></param>
        /// Writer：梁家健
        /// Create Date：2017-07-17
        public void CreateSizeRate(string SourceTableName, string TargetTableName)
        {
            try
            {
                var sqlUpdate = $@"update
	{TargetTableName} temp
set
	HaveDeep = 1
from
	(
		select
			max( IsDeep ) MaxDeep,
			CompanyID,
			ExtrSource,
			ShopCode,
			MaterialID,
			ModelID,
			SizeTypeFlag,
			SizeLength
		from
			{TargetTableName}
        where CompanyID = '{UserInfo.UserCompanyId}'
              and ExtrSource = '{UserInfo.UserExtrSource}'
		group by
			CompanyID,
			ExtrSource,
			ShopCode,
			MaterialID,
			ModelID,
			SizeTypeFlag,
			SizeLength
	) table1
where
	MaxDeep = 1
	and temp.CompanyID = table1.CompanyID
	and temp.ExtrSource = table1.ExtrSource
	and temp.ShopCode = table1.ShopCode
	and temp.MaterialID = table1.MaterialID
	and temp.ModelID = table1.ModelID
	and temp.SizeTypeFlag = table1.SizeTypeFlag
	and temp.SizeLength = table1.SizeLength";
                var sqlShop = $@"SELECT  temp.ExtrSource,
        temp.CompanyID ,
        temp.ID ,
        temp.ShopCode ,
        temp.ShopName ,
        temp.MaterialID ,
        temp.TotalQty ,
        temp.SizeLength ,
        shop.ShopID ,
        csr.SizeID ,
        CASE WHEN mmq.SizeID IS NULL THEN 0
             ELSE 1
        END AS IsDeep ,
        mmq.MinQty ,
        CASE WHEN mmq.MinQty IS NULL THEN 0
             ELSE 1
        END AS HaveDeep ,
        mt.ModelID ,
        stty.SizeTypeFlag ,
        area.AreaID AS CityID ,
        area.ParentID AS ProID ,
        apc.ParentAreaID ,
        csr.SizeRate
INTO    {TargetTableName}
FROM    {SourceTableName} temp
        LEFT JOIN ( SELECT  *
                    FROM    ndw.ndw_Shop
                    WHERE   ExtrSource = '{UserInfo.UserExtrSource}'
                            AND CompanyID = '{UserInfo.UserCompanyId}'
                  ) shop ON temp.ShopCode = shop.ShopCode
                            AND temp.ExtrSource = shop.ExtrSource
                            AND temp.CompanyID = shop.CompanyID
        LEFT JOIN ( SELECT  *
                    FROM    nods.nods_erp_Bas_Area
                    WHERE   ExtrSource = '{UserInfo.UserExtrSource}'
                  ) area ON shop.AreaID = area.AreaID
                            AND temp.ExtrSource = area.ExtrSource
        LEFT JOIN ( SELECT  *
                    FROM    ndw.ndw_AreaProCity
                    WHERE   ExtrSource = '{UserInfo.UserExtrSource}'
                            AND CompanyID = '{UserInfo.UserCompanyId}'
                  ) apc ON area.AreaID = apc.AreaID
                           AND temp.ExtrSource = apc.ExtrSource
                           AND temp.CompanyID = apc.CompanyID
        LEFT JOIN ( SELECT  *
                    FROM    nods.nods_erp_SD_Mat_Material
                    WHERE   ExtrSource = '{UserInfo.UserExtrSource}'
                  ) mt ON temp.MaterialID = mt.MaterialID
                          AND temp.ExtrSource = mt.ExtrSource
        LEFT JOIN ( SELECT  *
                    FROM    ndw.ndw_SKCSizeType
                    WHERE   ExtrSource = '{UserInfo.UserExtrSource}'
                            AND CompanyID = '{UserInfo.UserCompanyId}'
                  ) szt ON temp.MaterialID = szt.MaterialID
                           AND temp.ExtrSource = szt.ExtrSource
                           AND temp.CompanyID = szt.CompanyID
        LEFT JOIN ( SELECT  SizeTypeCode ,
                            SizeTypeFlag ,
                            ExtrSource ,
                            CompanyID
                    FROM    ndw.ndw_SizeTextType
                    WHERE   ExtrSource = '{UserInfo.UserExtrSource}'
                            AND CompanyID = '{UserInfo.UserCompanyId}'
                    GROUP BY SizeTypeCode ,
                            SizeTypeFlag ,
                            ExtrSource ,
                            CompanyID
                  ) stty ON szt.SizeTypeCode = stty.SizeTypeCode
                            AND temp.ExtrSource = stty.ExtrSource
                            AND temp.CompanyID = stty.CompanyID
        LEFT JOIN ( SELECT  *
                    FROM    ngoods.ngoods_DC_Bas_CitySizeRate
                    WHERE   ExtrSource = '{UserInfo.UserExtrSource}'
                            AND CompanyID = '{UserInfo.UserCompanyId}'
                  ) csr ON shop.AreaID = csr.AreaID
                           AND mt.ModelID = csr.ModelID
                           AND stty.SizeTypeFlag = csr.SizeTypeFlag
                           AND temp.ExtrSource = csr.ExtrSource
                           AND temp.CompanyID = csr.CompanyID
        LEFT JOIN ( SELECT  *
                    FROM    ngoods.ngoods_DC_Bas_ModelMinQty
                    WHERE   ExtrSource = '{UserInfo.UserExtrSource}'
                            AND CompanyID = '{UserInfo.UserCompanyId}'
                  ) mmq ON temp.CompanyID = mmq.CompanyID
                           AND temp.ExtrSource = mmq.ExtrSource
                           AND shop.ShopID = mmq.ShopID
                           AND mt.ModelID = mmq.ModelID
                           AND mt.ItemID = mmq.ItemID
                           AND csr.SizeID = mmq.SizeID
                           AND stty.SizeTypeFlag = mmq.SizeTypeFlag
                           AND temp.SizeLength = mmq.SizeLength";
                var sqlCity = $@"update
	{TargetTableName} temp
set
	IsDeep = table1.IsDeep,
	ModelID = table1.ModelID,
	SizeLength = table1.SizeLength
from
	(
		SELECT  temp.ExtrSource ,
        temp.CompanyID ,
        temp.ID ,
        temp.HaveDeep AS oldHaveDeep ,
        temp.MaterialID ,
        temp.SizeLength ,
        CASE WHEN mmq.SizeID IS NULL THEN 0
             ELSE 1
        END AS IsDeep ,
        mt.ModelID ,
        stty.SizeTypeFlag ,
        area.AreaID AS CityID ,
        area.ParentID AS ProID ,
        apc.ParentAreaID ,
        csr.SizeID ,
        csr.SizeRate
FROM    {TargetTableName} temp
        LEFT JOIN ( SELECT  *
                    FROM    ndw.ndw_Shop
                    WHERE   ExtrSource = '{UserInfo.UserExtrSource}'
                            AND CompanyID = '{UserInfo.UserCompanyId}'
                  ) shop ON temp.ShopCode = shop.ShopCode
                            AND temp.ExtrSource = shop.ExtrSource
                            AND temp.CompanyID = shop.CompanyID
        LEFT JOIN ( SELECT  *
                    FROM    nods.nods_erp_Bas_Area
                    WHERE   ExtrSource = '{UserInfo.UserExtrSource}'
                  ) area ON shop.AreaID = area.AreaID
                            AND temp.ExtrSource = area.ExtrSource
        LEFT JOIN ( SELECT  *
                    FROM    ndw.ndw_AreaProCity
                    WHERE   ExtrSource = '{UserInfo.UserExtrSource}'
                            AND CompanyID = '{UserInfo.UserCompanyId}'
                  ) apc ON area.AreaID = apc.AreaID
                           AND temp.ExtrSource = apc.ExtrSource
                           AND temp.CompanyID = apc.CompanyID
        LEFT JOIN ( SELECT  *
                    FROM    nods.nods_erp_SD_Mat_Material
                    WHERE   ExtrSource = '{UserInfo.UserExtrSource}'
                  ) mt ON temp.MaterialID = mt.MaterialID
                          AND temp.ExtrSource = mt.ExtrSource
        LEFT JOIN ( SELECT  *
                    FROM    ndw.ndw_SKCSizeType
                    WHERE   ExtrSource = '{UserInfo.UserExtrSource}'
                            AND CompanyID = '{UserInfo.UserCompanyId}'
                  ) szt ON temp.MaterialID = szt.MaterialID
                           AND temp.ExtrSource = szt.ExtrSource
                           AND temp.CompanyID = szt.CompanyID
        LEFT JOIN ( SELECT  SizeTypeCode ,
                            SizeTypeFlag ,
                            ExtrSource ,
                            CompanyID
                    FROM    ndw.ndw_SizeTextType
                    WHERE   ExtrSource = '{UserInfo.UserExtrSource}'
                            AND CompanyID = '{UserInfo.UserCompanyId}'
                    GROUP BY SizeTypeCode ,
                            SizeTypeFlag ,
                            ExtrSource ,
                            CompanyID
                  ) stty ON szt.SizeTypeCode = stty.SizeTypeCode
                            AND temp.ExtrSource = stty.ExtrSource
                            AND temp.CompanyID = stty.CompanyID
        LEFT JOIN ( SELECT  *
                    FROM    ngoods.ngoods_DC_Bas_CitySizeRate
                    WHERE   ExtrSource = '{UserInfo.UserExtrSource}'
                            AND CompanyID = '{UserInfo.UserCompanyId}'
                  ) csr ON shop.AreaID = csr.AreaID
                           AND mt.ModelID = csr.ModelID
                           AND stty.SizeTypeFlag = csr.SizeTypeFlag
                           AND temp.ExtrSource = csr.ExtrSource
                           AND temp.CompanyID = csr.CompanyID
        LEFT JOIN ( SELECT  mmq.* ,
                            area.AreaID ,
                            area.ParentID ,
                            apc.ParentAreaID
                    FROM    ngoods.ngoods_DC_Bas_ModelMinQty mmq
                            INNER JOIN ( SELECT *
                                         FROM   ndw.ndw_Shop
                                         WHERE  ExtrSource = '{UserInfo.UserExtrSource}'
                                                AND CompanyID = '{UserInfo.UserCompanyId}'
                                       ) shop ON mmq.ExtrSource = shop.ExtrSource
                                                 AND mmq.CompanyID = shop.CompanyID
                                                 AND mmq.ShopID = shop.ShopID
                            INNER JOIN ( SELECT *
                                         FROM   nods.nods_erp_Bas_Area
                                         WHERE  ExtrSource = '{UserInfo.UserExtrSource}'
                                       ) area ON mmq.ExtrSource = area.ExtrSource
                                                 AND shop.AreaID = area.AreaID
                            INNER  JOIN ( SELECT    *
                                          FROM      ndw.ndw_AreaProCity
                                          WHERE     ExtrSource = '{UserInfo.UserExtrSource}'
                                                    AND CompanyID = '{UserInfo.UserCompanyId}'
                                        ) apc ON area.AreaID = apc.AreaID
                                                 AND mmq.ExtrSource = apc.ExtrSource
                                                 AND mmq.CompanyID = apc.CompanyID
                    WHERE   mmq.ExtrSource = '{UserInfo.UserExtrSource}'
                            AND mmq.CompanyID = '{UserInfo.UserCompanyId}'
                  ) mmq ON temp.ExtrSource = mmq.ExtrSource
                           AND temp.CompanyID = mmq.CompanyID
                           AND shop.AreaID = mmq.AreaID
                           AND area.ParentID = mmq.ParentID
                           AND apc.ParentAreaID = mmq.ParentAreaID
                           AND mt.KindID = mmq.KindID
                           AND mt.ModelID = mmq.ModelID
                           AND mt.ItemID = mmq.ItemID
                           AND csr.SizeID = mmq.SizeID
                           AND stty.SizeTypeFlag = mmq.SizeTypeFlag
                           AND temp.SizeLength = mmq.SizeLength
WHERE   temp.HaveDeep = 0
        AND temp.ExtrSource = '{UserInfo.UserExtrSource}'
        AND temp.CompanyID = '{UserInfo.UserCompanyId}'
GROUP BY temp.ExtrSource ,
        temp.CompanyID ,
        temp.ID ,
        temp.HaveDeep ,
        temp.MaterialID ,
        temp.SizeLength ,
        mmq.SizeID ,
        mt.ModelID ,
        stty.SizeTypeFlag ,
        area.AreaID ,
        area.ParentID ,
        apc.ParentAreaID ,
        csr.SizeID ,
        csr.SizeRate
	) table1
where temp.ExtrSource = table1.ExtrSource
	and temp.CompanyID = table1.CompanyID
	and	temp.CityID = table1.CityID
	and temp.ProID=table1.ProID
	and temp.ParentAreaID=table1.ParentAreaID
	and temp.SizeID = table1.SizeID
	and temp.MaterialID = table1.MaterialID
	and temp.SizeLength = table1.SizeLength
	and temp.SizeTypeFlag = table1.SizeTypeFlag
	and temp.ID = table1.ID";
                var sqlPro = $@"update
	{TargetTableName} temp
set
	IsDeep = table1.IsDeep,
	ModelID = table1.ModelID,
	SizeLength = table1.SizeLength
from
	(
		SELECT  temp.ExtrSource ,
        temp.CompanyID ,
        temp.ID ,
        temp.HaveDeep AS oldHaveDeep ,
        temp.MaterialID ,
        temp.SizeLength ,
        CASE WHEN mmq.SizeID IS NULL THEN 0
             ELSE 1
        END AS IsDeep ,
        mt.ModelID ,
        stty.SizeTypeFlag ,
        area.ParentID AS ProID ,
        apc.ParentAreaID ,
        csr.SizeID ,
        csr.SizeRate
FROM    {TargetTableName} temp
        LEFT JOIN ( SELECT  *
                    FROM    ndw.ndw_Shop
                    WHERE   ExtrSource = '{UserInfo.UserExtrSource}'
                            AND CompanyID = '{UserInfo.UserCompanyId}'
                  ) shop ON temp.ShopCode = shop.ShopCode
                            AND temp.ExtrSource = shop.ExtrSource
                            AND temp.CompanyID = shop.CompanyID
        LEFT JOIN ( SELECT  *
                    FROM    nods.nods_erp_Bas_Area
                    WHERE   ExtrSource = '{UserInfo.UserExtrSource}'
                  ) area ON shop.AreaID = area.AreaID
                            AND temp.ExtrSource = area.ExtrSource
        LEFT JOIN ( SELECT  *
                    FROM    ndw.ndw_AreaProCity
                    WHERE   ExtrSource = '{UserInfo.UserExtrSource}'
                            AND CompanyID = '{UserInfo.UserCompanyId}'
                  ) apc ON area.AreaID = apc.AreaID
                           AND temp.ExtrSource = apc.ExtrSource
                           AND temp.CompanyID = apc.CompanyID
        LEFT JOIN ( SELECT  *
                    FROM    nods.nods_erp_SD_Mat_Material
                    WHERE   ExtrSource = '{UserInfo.UserExtrSource}'
                  ) mt ON temp.MaterialID = mt.MaterialID
                          AND temp.ExtrSource = mt.ExtrSource
        LEFT JOIN ( SELECT  *
                    FROM    ndw.ndw_SKCSizeType
                    WHERE   ExtrSource = '{UserInfo.UserExtrSource}'
                            AND CompanyID = '{UserInfo.UserCompanyId}'
                  ) szt ON temp.MaterialID = szt.MaterialID
                           AND temp.ExtrSource = szt.ExtrSource
                           AND temp.CompanyID = szt.CompanyID
        LEFT JOIN ( SELECT  SizeTypeCode ,
                            SizeTypeFlag ,
                            ExtrSource ,
                            CompanyID
                    FROM    ndw.ndw_SizeTextType
                    WHERE   ExtrSource = '{UserInfo.UserExtrSource}'
                            AND CompanyID = '{UserInfo.UserCompanyId}'
                    GROUP BY SizeTypeCode ,
                            SizeTypeFlag ,
                            ExtrSource ,
                            CompanyID
                  ) stty ON szt.SizeTypeCode = stty.SizeTypeCode
                            AND temp.ExtrSource = stty.ExtrSource
                            AND temp.CompanyID = stty.CompanyID
        LEFT JOIN ( SELECT  *
                    FROM    ngoods.ngoods_DC_Bas_CitySizeRate
                    WHERE   ExtrSource = '{UserInfo.UserExtrSource}'
                            AND CompanyID = '{UserInfo.UserCompanyId}'
                  ) csr ON shop.AreaID = csr.AreaID
                           AND mt.ModelID = csr.ModelID
                           AND stty.SizeTypeFlag = csr.SizeTypeFlag
                           AND temp.ExtrSource = csr.ExtrSource
                           AND temp.CompanyID = csr.CompanyID
        LEFT JOIN ( SELECT  mmq.* ,
                            area.AreaID ,
                            area.ParentID ,
                            apc.ParentAreaID
                    FROM    ngoods.ngoods_DC_Bas_ModelMinQty mmq
                            INNER JOIN ( SELECT *
                                         FROM   ndw.ndw_Shop
                                         WHERE  ExtrSource = '{UserInfo.UserExtrSource}'
                                                AND CompanyID = '{UserInfo.UserCompanyId}'
                                       ) shop ON mmq.ExtrSource = shop.ExtrSource
                                                 AND mmq.CompanyID = shop.CompanyID
                                                 AND mmq.ShopID = shop.ShopID
                            INNER JOIN ( SELECT *
                                         FROM   nods.nods_erp_Bas_Area
                                         WHERE  ExtrSource = '{UserInfo.UserExtrSource}'
                                       ) area ON mmq.ExtrSource = area.ExtrSource
                                                 AND shop.AreaID = area.AreaID
                            INNER  JOIN ( SELECT    *
                                          FROM      ndw.ndw_AreaProCity
                                          WHERE     ExtrSource = '{UserInfo.UserExtrSource}'
                                                    AND CompanyID = '{UserInfo.UserCompanyId}'
                                        ) apc ON area.AreaID = apc.AreaID
                                                 AND mmq.ExtrSource = apc.ExtrSource
                                                 AND mmq.CompanyID = apc.CompanyID
                    WHERE   mmq.ExtrSource = '{UserInfo.UserExtrSource}'
                            AND mmq.CompanyID = '{UserInfo.UserCompanyId}'
                  ) mmq ON temp.ExtrSource = mmq.ExtrSource
                           AND temp.CompanyID = mmq.CompanyID
                           AND area.ParentID = mmq.ParentID
                           AND apc.ParentAreaID = mmq.ParentAreaID
                           AND mt.ModelID = mmq.ModelID
                           AND mt.ModelID = mmq.ModelID
                           AND mt.ModelID = mmq.ModelID
                           AND mt.ItemID = mmq.ItemID
                           AND csr.SizeID = mmq.SizeID
                           AND stty.SizeTypeFlag = mmq.SizeTypeFlag
                           AND temp.SizeLength = mmq.SizeLength
WHERE   temp.HaveDeep = 0
        AND temp.ExtrSource = '{UserInfo.UserExtrSource}'
        AND temp.CompanyID = '{UserInfo.UserCompanyId}'
GROUP BY temp.ExtrSource ,
        temp.CompanyID ,
        temp.ID ,
        temp.HaveDeep ,
        temp.MaterialID ,
        temp.SizeLength ,
        mmq.SizeID ,
        mt.ModelID ,
        stty.SizeTypeFlag ,
        area.ParentID ,
        apc.ParentAreaID ,
        csr.SizeID ,
        csr.SizeRate
	) table1
where temp.ExtrSource = table1.ExtrSource
	and temp.CompanyID = table1.CompanyID
	and	 temp.ProID = table1.ProID
	and temp.ParentAreaID = table1.ParentAreaID
	and temp.SizeID = table1.SizeID
	and temp.MaterialID = table1.MaterialID
	and temp.SizeLength = table1.SizeLength
	and temp.SizeTypeFlag = table1.SizeTypeFlag
	and temp.ID = table1.ID";
                var sqlParentArea = $@"update
	{TargetTableName} temp
set
	IsDeep = table1.IsDeep,
	ModelID = table1.ModelID,
	SizeLength = table1.SizeLength
from
	(
		SELECT  temp.ExtrSource ,
        temp.CompanyID ,
        temp.ID ,
        temp.HaveDeep AS oldHaveDeep ,
        temp.MaterialID ,
        temp.SizeLength ,
        CASE WHEN mmq.SizeID IS NULL THEN 0
             ELSE 1
        END AS IsDeep ,
        mt.ModelID ,
        stty.SizeTypeFlag ,
        apc.ParentAreaID ,
        csr.SizeID ,
        csr.SizeRate
FROM    {TargetTableName} temp
        LEFT JOIN ( SELECT  *
                    FROM    ndw.ndw_Shop
                    WHERE   ExtrSource = '{UserInfo.UserExtrSource}'
                            AND CompanyID = '{UserInfo.UserCompanyId}'
                  ) shop ON temp.ShopCode = shop.ShopCode
                            AND temp.ExtrSource = shop.ExtrSource
                            AND temp.CompanyID = shop.CompanyID
        LEFT JOIN ( SELECT  *
                    FROM    nods.nods_erp_Bas_Area
                    WHERE   ExtrSource = '{UserInfo.UserExtrSource}'
                  ) area ON shop.AreaID = area.AreaID
                            AND temp.ExtrSource = area.ExtrSource
        LEFT JOIN ( SELECT  *
                    FROM    ndw.ndw_AreaProCity
                    WHERE   ExtrSource = '{UserInfo.UserExtrSource}'
                            AND CompanyID = '{UserInfo.UserCompanyId}'
                  ) apc ON area.AreaID = apc.AreaID
                           AND temp.ExtrSource = apc.ExtrSource
                           AND temp.CompanyID = apc.CompanyID
        LEFT JOIN ( SELECT  *
                    FROM    nods.nods_erp_SD_Mat_Material
                    WHERE   ExtrSource = '{UserInfo.UserExtrSource}'
                  ) mt ON temp.MaterialID = mt.MaterialID
                          AND temp.ExtrSource = mt.ExtrSource
        LEFT JOIN ( SELECT  *
                    FROM    ndw.ndw_SKCSizeType
                    WHERE   ExtrSource = '{UserInfo.UserExtrSource}'
                            AND CompanyID = '{UserInfo.UserCompanyId}'
                  ) szt ON temp.MaterialID = szt.MaterialID
                           AND temp.ExtrSource = szt.ExtrSource
                           AND temp.CompanyID = szt.CompanyID
        LEFT JOIN ( SELECT  SizeTypeCode ,
                            SizeTypeFlag ,
                            ExtrSource ,
                            CompanyID
                    FROM    ndw.ndw_SizeTextType
                    WHERE   ExtrSource = '{UserInfo.UserExtrSource}'
                            AND CompanyID = '{UserInfo.UserCompanyId}'
                    GROUP BY SizeTypeCode ,
                            SizeTypeFlag ,
                            ExtrSource ,
                            CompanyID
                  ) stty ON szt.SizeTypeCode = stty.SizeTypeCode
                            AND temp.ExtrSource = stty.ExtrSource
                            AND temp.CompanyID = stty.CompanyID
        LEFT JOIN ( SELECT  *
                    FROM    ngoods.ngoods_DC_Bas_CitySizeRate
                    WHERE   ExtrSource = '{UserInfo.UserExtrSource}'
                            AND CompanyID = '{UserInfo.UserCompanyId}'
                  ) csr ON shop.AreaID = csr.AreaID
                           AND mt.ModelID = csr.ModelID
                           AND stty.SizeTypeFlag = csr.SizeTypeFlag
                           AND temp.ExtrSource = csr.ExtrSource
                           AND temp.CompanyID = csr.CompanyID
        LEFT JOIN ( SELECT  mmq.* ,
                            area.AreaID ,
                            area.ParentID ,
                            apc.ParentAreaID
                    FROM    ngoods.ngoods_DC_Bas_ModelMinQty mmq
                            INNER JOIN ( SELECT *
                                         FROM   ndw.ndw_Shop
                                         WHERE  ExtrSource = '{UserInfo.UserExtrSource}'
                                                AND CompanyID = '{UserInfo.UserCompanyId}'
                                       ) shop ON mmq.ExtrSource = shop.ExtrSource
                                                 AND mmq.CompanyID = shop.CompanyID
                                                 AND mmq.ShopID = shop.ShopID
                            INNER JOIN ( SELECT *
                                         FROM   nods.nods_erp_Bas_Area
                                         WHERE  ExtrSource = '{UserInfo.UserExtrSource}'
                                       ) area ON mmq.ExtrSource = area.ExtrSource
                                                 AND shop.AreaID = area.AreaID
                            INNER  JOIN ( SELECT    *
                                          FROM      ndw.ndw_AreaProCity
                                          WHERE     ExtrSource = '{UserInfo.UserExtrSource}'
                                                    AND CompanyID = '{UserInfo.UserCompanyId}'
                                        ) apc ON area.AreaID = apc.AreaID
                                                 AND mmq.ExtrSource = apc.ExtrSource
                                                 AND mmq.CompanyID = apc.CompanyID
                    WHERE   mmq.ExtrSource = '{UserInfo.UserExtrSource}'
                            AND mmq.CompanyID = '{UserInfo.UserCompanyId}'
                  ) mmq ON temp.ExtrSource = mmq.ExtrSource
                           AND temp.CompanyID = mmq.CompanyID
                           AND apc.ParentAreaID = mmq.ParentAreaID
                           AND mt.ModelID = mmq.ModelID
                           AND mt.ModelID = mmq.ModelID
                           AND mt.ModelID = mmq.ModelID
                           AND mt.ItemID = mmq.ItemID
                           AND csr.SizeID = mmq.SizeID
                           AND stty.SizeTypeFlag = mmq.SizeTypeFlag
                           AND temp.SizeLength = mmq.SizeLength
WHERE   temp.HaveDeep = 0
        AND temp.ExtrSource = '{UserInfo.UserExtrSource}'
        AND temp.CompanyID = '{UserInfo.UserCompanyId}'
GROUP BY temp.ExtrSource ,
        temp.CompanyID ,
        temp.ID ,
        temp.HaveDeep ,
        temp.MaterialID ,
        temp.SizeLength ,
        mmq.SizeID ,
        mt.ModelID ,
        stty.SizeTypeFlag ,
        apc.ParentAreaID ,
        csr.SizeID ,
        csr.SizeRate
	) table1
where temp.ExtrSource = table1.ExtrSource
	and temp.CompanyID = table1.CompanyID
	and	temp.ParentAreaID = table1.ParentAreaID
	and temp.SizeID = table1.SizeID
	and temp.MaterialID = table1.MaterialID
	and temp.SizeLength = table1.SizeLength
	and temp.SizeTypeFlag = table1.SizeTypeFlag
	and temp.ID = table1.ID";
                var sqlCountry = $@"update
	{TargetTableName} temp
set
	IsDeep = table1.IsDeep,
	ModelID = table1.ModelID,
	SizeLength = table1.SizeLength
from
	(
		SELECT  temp.ExtrSource ,
        temp.CompanyID ,
        temp.ID ,
        temp.HaveDeep AS oldHaveDeep ,
        temp.MaterialID ,
        temp.SizeLength ,
        CASE WHEN mmq.SizeID IS NULL THEN 0
             ELSE 1
        END AS IsDeep ,
        mt.ModelID ,
        stty.SizeTypeFlag ,
        csr.SizeID ,
        csr.SizeRate
FROM    {TargetTableName} temp
        LEFT JOIN ( SELECT  *
                    FROM    ndw.ndw_Shop
                    WHERE   ExtrSource = '{UserInfo.UserExtrSource}'
                            AND CompanyID = '{UserInfo.UserCompanyId}'
                  ) shop ON temp.ShopCode = shop.ShopCode
                            AND temp.ExtrSource = shop.ExtrSource
                            AND temp.CompanyID = shop.CompanyID
        LEFT JOIN ( SELECT  *
                    FROM    nods.nods_erp_Bas_Area
                    WHERE   ExtrSource = '{UserInfo.UserExtrSource}'
                  ) area ON shop.AreaID = area.AreaID
                            AND temp.ExtrSource = area.ExtrSource
        LEFT JOIN ( SELECT  *
                    FROM    ndw.ndw_AreaProCity
                    WHERE   ExtrSource = '{UserInfo.UserExtrSource}'
                            AND CompanyID = '{UserInfo.UserCompanyId}'
                  ) apc ON area.AreaID = apc.AreaID
                           AND temp.ExtrSource = apc.ExtrSource
                           AND temp.CompanyID = apc.CompanyID
        LEFT JOIN ( SELECT  *
                    FROM    nods.nods_erp_SD_Mat_Material
                    WHERE   ExtrSource = '{UserInfo.UserExtrSource}'
                  ) mt ON temp.MaterialID = mt.MaterialID
                          AND temp.ExtrSource = mt.ExtrSource
        LEFT JOIN ( SELECT  *
                    FROM    ndw.ndw_SKCSizeType
                    WHERE   ExtrSource = '{UserInfo.UserExtrSource}'
                            AND CompanyID = '{UserInfo.UserCompanyId}'
                  ) szt ON temp.MaterialID = szt.MaterialID
                           AND temp.ExtrSource = szt.ExtrSource
                           AND temp.CompanyID = szt.CompanyID
        LEFT JOIN ( SELECT  SizeTypeCode ,
                            SizeTypeFlag ,
                            ExtrSource ,
                            CompanyID
                    FROM    ndw.ndw_SizeTextType
                    WHERE   ExtrSource = '{UserInfo.UserExtrSource}'
                            AND CompanyID = '{UserInfo.UserCompanyId}'
                    GROUP BY SizeTypeCode ,
                            SizeTypeFlag ,
                            ExtrSource ,
                            CompanyID
                  ) stty ON szt.SizeTypeCode = stty.SizeTypeCode
                            AND temp.ExtrSource = stty.ExtrSource
                            AND temp.CompanyID = stty.CompanyID
        LEFT JOIN ( SELECT  *
                    FROM    ngoods.ngoods_DC_Bas_CitySizeRate
                    WHERE   ExtrSource = '{UserInfo.UserExtrSource}'
                            AND CompanyID = '{UserInfo.UserCompanyId}'
                  ) csr ON shop.AreaID = csr.AreaID
                           AND mt.ModelID = csr.ModelID
                           AND stty.SizeTypeFlag = csr.SizeTypeFlag
                           AND temp.ExtrSource = csr.ExtrSource
                           AND temp.CompanyID = csr.CompanyID
        LEFT JOIN ( SELECT  mmq.* ,
                            area.AreaID ,
                            area.ParentID ,
                            apc.ParentAreaID
                    FROM    ngoods.ngoods_DC_Bas_ModelMinQty mmq
                            INNER JOIN ( SELECT *
                                         FROM   ndw.ndw_Shop
                                         WHERE  ExtrSource = '{UserInfo.UserExtrSource}'
                                                AND CompanyID = '{UserInfo.UserCompanyId}'
                                       ) shop ON mmq.ExtrSource = shop.ExtrSource
                                                 AND mmq.CompanyID = shop.CompanyID
                                                 AND mmq.ShopID = shop.ShopID
                            INNER JOIN ( SELECT *
                                         FROM   nods.nods_erp_Bas_Area
                                         WHERE  ExtrSource = '{UserInfo.UserExtrSource}'
                                       ) area ON mmq.ExtrSource = area.ExtrSource
                                                 AND shop.AreaID = area.AreaID
                            INNER  JOIN ( SELECT    *
                                          FROM      ndw.ndw_AreaProCity
                                          WHERE     ExtrSource = '{UserInfo.UserExtrSource}'
                                                    AND CompanyID = '{UserInfo.UserCompanyId}'
                                        ) apc ON area.AreaID = apc.AreaID
                                                 AND mmq.ExtrSource = apc.ExtrSource
                                                 AND mmq.CompanyID = apc.CompanyID
                    WHERE   mmq.ExtrSource = '{UserInfo.UserExtrSource}'
                            AND mmq.CompanyID = '{UserInfo.UserCompanyId}'
                  ) mmq ON temp.ExtrSource = mmq.ExtrSource
                           AND temp.CompanyID = mmq.CompanyID
                           AND mt.ModelID = mmq.ModelID
                           AND mt.ModelID = mmq.ModelID
                           AND mt.ModelID = mmq.ModelID
                           AND mt.ItemID = mmq.ItemID
                           AND csr.SizeID = mmq.SizeID
                           AND stty.SizeTypeFlag = mmq.SizeTypeFlag
                           AND temp.SizeLength = mmq.SizeLength
WHERE   temp.HaveDeep = 0
        AND temp.ExtrSource = '{UserInfo.UserExtrSource}'
        AND temp.CompanyID = '{UserInfo.UserCompanyId}'
GROUP BY temp.ExtrSource ,
        temp.CompanyID ,
        temp.ID ,
        temp.HaveDeep ,
        temp.MaterialID ,
        temp.SizeLength ,
        mmq.SizeID ,
        mt.ModelID ,
        stty.SizeTypeFlag ,
        csr.SizeID ,
        csr.SizeRate
	) table1
where temp.ExtrSource = table1.ExtrSource
	and temp.CompanyID = table1.CompanyID
	and	temp.SizeID = table1.SizeID
	and temp.MaterialID = table1.MaterialID
	and temp.SizeLength = table1.SizeLength
	and temp.SizeTypeFlag = table1.SizeTypeFlag
	and temp.ID = table1.ID";
                var cmdList = new List<CommandInfo>
                {
                    new CommandInfo
                    {
                        CommandText = sqlShop,
                    },
                    new CommandInfo
                    {
                        CommandText = sqlUpdate,
                    },
                    new CommandInfo
                    {
                        CommandText = sqlCity,
                    },
                    new CommandInfo
                    {
                        CommandText = sqlUpdate,
                    },
                    new CommandInfo
                    {
                        CommandText = sqlPro,
                    },
                    new CommandInfo
                    {
                        CommandText = sqlUpdate,
                    },
                    new CommandInfo
                    {
                        CommandText = sqlParentArea,
                    },
                    new CommandInfo
                    {
                        CommandText = sqlUpdate,
                    },
                    new CommandInfo
                    {
                        CommandText = sqlCountry,
                    },
                    new CommandInfo
                    {
                        CommandText = sqlUpdate,
                    },
                };
                Helper.ExecuteSqlTran(cmdList);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        public IList<NewPlanningOrderQuantityModel.TempModel> ReturnTempData(string TargetTableName)
        {
            try
            {
                var sql = $@"SELECT  ID ,
        CompanyID ,
        ExtrSource ,
        ShopCode ,
        ShopName ,
        MaterialID ,
        TotalQty ,
        SizeLength ,
        ShopID ,
        SizeID ,
        IsDeep ,
        ISNULL(MinQty, 0) MinQty ,
        HaveDeep ,
        ModelID ,
        SizeTypeFlag ,
        CityID ,
        ProID ,
        ParentAreaID ,
        SizeRate
FROM    {TargetTableName} 
ORDER BY CompanyID ,
        ExtrSource ,
        ShopID ,
        MaterialID ,
        ModelID ,
        SizeID";
                var cmd = Helper.GetSqlStringCommand(sql);
                var dt = Helper.ExecuteDataTable(cmd);
                return ModelConvertHelper<NewPlanningOrderQuantityModel.TempModel>.ConvertToModel(dt);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        
        #endregion Excel导入
    }
}
